package com.woniu.mapper;

import com.woniu.been.entity.Courses;
import com.woniu.been.entity.UserOrders;
import com.woniu.been.entity.Users;
import com.woniu.been.entity.vo.UserOrdersVo;
import org.apache.ibatis.annotations.*;

import java.util.List;
@Mapper
public interface UserOrdersMapper {
    /** 查询所有订单*/
    List<UserOrders> findAll(UserOrders userOrders);

    /** 添加订单*/

    int insertOrder(UserOrders userOrders);
    /**
     * 修改订单
     *
     * @return
     */
    @Update("UPDATE user_orders SET payment_status = #{paymentStatus}, order_date = NOW() WHERE order_number = #{orderNumber}")
    int updateById(UserOrders userOrders);

    /** 删除订单*/
    @Delete("delete from user_orders where order_id=#{order_id}")
    int delById(String order_id);

   @Insert("insert into user_course(user_id,course_id) values(#{userId},#{courseId})")
    int insertUserAndCourses(UserOrders userOrders);

   @Select("select * from users where user_id=#{userId}")
    Users findUserById(Integer userId);

    List<UserOrders> QueryOrdersByPagination(UserOrdersVo userOrdersVo);

    //根据电话号码

    @Select("SELECT * FROM user_orders WHERE user_id = #{userId} AND course_id = #{courseId} " +
            "ORDER BY create_time DESC LIMIT 1")
    UserOrders findLatestOrderByUserAndCourse(@Param("userId") Integer userId,
                                              @Param("courseId") Integer courseId);

    @Update("UPDATE user_orders SET payment_status = #{paymentStatus} WHERE order_id = #{orderId}")
    int updateOrderStatus(@Param("orderId") Integer orderId,
                          @Param("paymentStatus") String status);

    @Select("SELECT * FROM user_orders WHERE order_number = #{orderNumber}")
    List<UserOrders> findOrderByNumber(String orderNumber);
    @Update("UPDATE user_orders SET payment_status = '无效订单' WHERE order_number = #{orderNumber}")
    void updateByNumber(String orderNumber);
   @Select("select * from user_orders where course_id = #{courseId} AND user_id = #{userId}")
   UserOrders findNumberByCourseId( @Param("courseId")Integer courseId, @Param("userId")Integer userId);


   @Select("SELECT  AVG(payment_amount)  FROM  user_orders WHERE payment_status = '已完成'")
    Double averageOrderPrice();

   @Select("SELECT SUM(payment_amount)  FROM user_orders WHERE payment_status = '已完成'")
    Double totalSales();

   @Select("SELECT COUNT(*) FROM user_orders")
    Integer totalOrders();

   @Select("SELECT  c.course_name,COUNT(o.order_id) AS salesCount FROM user_orders o JOIN courses c ON o.course_id = c.course_id GROUP BY o.course_id ORDER BY sales_count DESC")
    List<Courses> courseSalesRanking();

   @Select("SELECT c.course_name,SUM(o.payment_amount) AS salesAmount FROM user_orders o JOIN courses c ON o.course_id = c.course_id GROUP BY o.course_id ORDER BY sales_amount DESC")
    List<Courses> courseSalesRankingByIncome();

}
